package com.java.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.java.model.Diary;
import com.java.model.DiaryType;

public class DiaryTypeDao {

	//日志类别显示
	public List<DiaryType> DiaryTypeCountList(Connection con)throws Exception{
		List<DiaryType> diaryTypeCountList=new ArrayList<DiaryType>();
		String sql="select diaryTypeId,typeName,count(diaryId) as diaryCount from t_diary right join t_diarytype on t_diary.typeId=t_diarytype.diaryTypeId group by typeName;";
		PreparedStatement pstmt=con.prepareStatement(sql);
		ResultSet rs=pstmt.executeQuery();
		while(rs.next()){
			DiaryType diaryType=new DiaryType();
			diaryType.setDiaryTypeId(rs.getInt("diaryTypeId"));
			diaryType.setTypeName(rs.getString("typeName"));
			diaryType.setDiaryCount(rs.getInt("diaryCount"));
			diaryTypeCountList.add(diaryType);
		}
		return diaryTypeCountList;
	}

	//日志分类管理
	public List<DiaryType> DiaryTypeList(Connection con) throws Exception{
		List<DiaryType> diaryTypeList=new ArrayList<DiaryType>();
		String sql="select * from t_diarytype";
		PreparedStatement pstmt=con.prepareStatement(sql);
		ResultSet rs=pstmt.executeQuery();
		while(rs.next()){
			DiaryType diaryType=new DiaryType();
			diaryType.setDiaryTypeId(rs.getInt("diaryTypeId"));
			diaryType.setTypeName(rs.getString("typeName"));
			diaryTypeList.add(diaryType);
		}
		return diaryTypeList;
	}

	//日志类别添加
    public int diaryTypeAdd(Connection con,DiaryType diaryType) throws Exception{
	    String sql="insert into t_diarytype values(null,?)";
	    PreparedStatement pstmt=con.prepareStatement(sql);
	    pstmt.setString(1,diaryType.getTypeName());
	    return pstmt.executeUpdate();
    }

    //日志类别修改
    public int diaryTypeUpdate(Connection con,DiaryType diaryType) throws Exception{
        String sql="update t_diarytype set typeName=? where diaryTypeId=?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setString(1,diaryType.getTypeName());
        pstmt.setInt(2,diaryType.getDiaryTypeId());
        return pstmt.executeUpdate();
    }

    //根据日志Id显示日志类别
    public DiaryType diaryTypeShow(Connection con,String diaryTypeId) throws Exception{
	    DiaryType diaryType=new DiaryType();
	    String sql="select * from t_diarytype where diaryTypeId=?";
	    PreparedStatement pstmt=con.prepareStatement(sql);
	    pstmt.setString(1,diaryTypeId);
	    ResultSet rs=pstmt.executeQuery();
	    if(rs.next()){
	        diaryType.setDiaryTypeId(rs.getInt("diaryTypeId"));
	        diaryType.setTypeName(rs.getString("typeName"));
        }
	    return diaryType;
    }

    //日志类别删除
    public int diaryTypeDelete(Connection con, String diaryTypeId) throws Exception{
	    String sql="delete from t_diarytype where diaryTypeId=?";
        PreparedStatement pstmt=con.prepareStatement(sql);
        pstmt.setString(1,diaryTypeId);
        return pstmt.executeUpdate();
    }
}
